package cn.yi.medicalsys.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.yi.medicalsys.dao.DoctorDao;
import cn.yi.medicalsys.pojo.Doctor;
import cn.yi.medicalsys.utils.BaseDao;

public class DoctorDaoImpl extends BaseDao implements DoctorDao {

	@Override
	public int findTotalCount(String docName, String office) throws Exception {
		int row = -1;
		StringBuffer preparedSql = new StringBuffer();
		List<Object> listParams = new ArrayList<Object>();
		preparedSql
				.append("SELECT COUNT(1) FROM doctor INNER JOIN office ON doctor.`oid`=office.`oid` where 1=1 ");
		if (docName != null && !docName.equals("")) {
			preparedSql.append("AND doctName=?  ");
			listParams.add(docName);
		}
		if (office != null && !office.equals("")) {
			preparedSql.append("AND office.`officeName`=?  ");
			listParams.add(office);
		}
		Object[] params = listParams.toArray();
		System.out.println(preparedSql.toString());
		ResultSet rs = super.executeQuery(preparedSql.toString(), params);
		while (rs != null && rs.next()) {
			row = rs.getInt(1);
		}
		super.closeResource(rs);
		return row;
	}

	@Override
	public List<Doctor> findDoctorByOffset(int offer, int pageSize,
			String docName, String office) throws Exception {
		Doctor doctor = null;
		List<Doctor> list = new ArrayList<Doctor>();
		StringBuffer preparedSql = new StringBuffer();
		List<Object> listParams = new ArrayList<Object>();
		preparedSql
				.append("SELECT did,hid,doctor.oid,doctName,doctSex,doctAge,doctPhone,appellation,");
		preparedSql.append("doctEmail,doctIntr,doctImage ");
		preparedSql
				.append("FROM doctor INNER JOIN office ON doctor.`oid`=office.`oid` where 1=1 ");
		if (docName != null && !docName.equals("")) {
			preparedSql.append("AND doctName=?  ");
			listParams.add(docName);
		}
		if (office != null && !office.equals("")) {
			preparedSql.append("AND office.`officeName`=?  ");
			listParams.add(office);
		}
		preparedSql.append("ORDER BY did LIMIT ?,?  ");
		listParams.add(offer);
		listParams.add(pageSize);
		Object[] params = listParams.toArray();
		ResultSet rs = super.executeQuery(preparedSql.toString(), params);
		while (rs.next()) {
			doctor = new Doctor();
			doctor.setdId(rs.getInt("did"));
			doctor.sethId(rs.getInt("hid"));
			doctor.setoId(rs.getInt("doctor.oid"));
			doctor.setDoctName(rs.getString("doctName"));
			doctor.setDoctSex(rs.getString("doctSex"));
			doctor.setDoctAge(rs.getInt("doctAge"));
			doctor.setDoctPhone(rs.getString("doctPhone"));
			doctor.setAppellation(rs.getString("appellation"));
			doctor.setDoctEmail(rs.getString("doctEmail"));
			doctor.setDoctIntr(rs.getString("doctIntr"));
			doctor.setDoctImage(rs.getString("doctImage"));
			list.add(doctor);
		}
		super.closeResource(rs);
		return list;
	}

	@Override
	public int updateDoctor(Integer doctAge, String appellation,
			String doctPhone, String doctEmail, Integer did, Integer officeid)
			throws Exception {
		StringBuffer preparedSql = new StringBuffer();
		List<Object> listParams = new ArrayList<Object>();
		preparedSql.append("UPDATE doctor SET did=? ");
		listParams.add(did);
		if (doctAge > 0) {
			preparedSql.append(",doctAge=? ");
			listParams.add(doctAge);
		}
		if (doctPhone != null && !doctPhone.equals("")) {
			preparedSql.append(",doctPhone=? ");
			listParams.add(doctPhone);
		}
		if (appellation != null && !appellation.equals("")) {
			preparedSql.append(",appellation=? ");
			listParams.add(appellation);
		}
		if (doctEmail != null && !doctEmail.equals("")) {
			preparedSql.append(",doctEmail=? ");
			listParams.add(doctEmail);
		}
		if (officeid > 0) {
			preparedSql.append(",oid=? ");
			listParams.add(officeid);
		}
		preparedSql.append("WHERE did=?");
		listParams.add(did);
		System.out.println(preparedSql.toString());
		Object[] params = listParams.toArray();
		int row = super.executeUpdate(preparedSql.toString(), params);
		return row;
	}

	@Override
	public int addDoctor(Doctor doctor) throws Exception {
		StringBuffer preparedSql = new StringBuffer();
		preparedSql
				.append("INSERT INTO doctor(hid,oid,doctName,doctSex,doctAge,doctPhone,appellation,doctEmail,doctIntr,doctImage) ");
		preparedSql.append("VALUE(?,?,?,?,?,?,?,?,?,?)");
		Object[] params = { doctor.gethId(), doctor.getoId(),
				doctor.getDoctName(), doctor.getDoctSex(), doctor.getDoctAge(),
				doctor.getDoctPhone(), doctor.getAppellation(),
				doctor.getDoctEmail(), doctor.getDoctIntr(),
				doctor.getDoctImage() };
		System.out.println(preparedSql);
		int row = super.executeUpdate(preparedSql.toString(), params);
		return row;
	}

	@Override
	public Doctor findOfficeByNo(Integer did) throws Exception {
		Doctor doctor = null;
		StringBuffer preparedSql = new StringBuffer();
		preparedSql
				.append("SELECT did,hid,oid,doctName,doctSex,doctAge,doctPhone,appellation,");
		preparedSql.append("doctEmail,doctIntr,doctImage ");
		preparedSql.append("FROM doctor where did=?  ");
		Object[] params = { did };
		ResultSet rs = super.executeQuery(preparedSql.toString(), params);
		while (rs.next()) {
			doctor = new Doctor();
			doctor.setdId(rs.getInt("did"));
			doctor.sethId(rs.getInt("hid"));
			doctor.setoId(rs.getInt("doctor.oid"));
			doctor.setDoctName(rs.getString("doctName"));
			doctor.setDoctSex(rs.getString("doctSex"));
			doctor.setDoctAge(rs.getInt("doctAge"));
			doctor.setDoctPhone(rs.getString("doctPhone"));
			doctor.setAppellation(rs.getString("appellation"));
			doctor.setDoctEmail(rs.getString("doctEmail"));
			doctor.setDoctIntr(rs.getString("doctIntr"));
			doctor.setDoctImage(rs.getString("doctImage"));
		}
		super.closeResource(rs);
		return doctor;
	}

	@Override
	public int deleteDoctor(Integer did) throws Exception {
		String preparedSql = "SELECT count(1) FROM register where did=?  ";// 查询
		String preparedSql1 = "delete from doctor where did=?  ";// 删除
		String preparedSql2 = "DELETE FROM register WHERE did=?  ";// 删除含外键
		Object[] params = { did };
		int row = -1;		
		ResultSet rs = super.executeQuery(preparedSql, params);		
		if (rs.next() && rs.getInt(1) > 0) {
			if(super.executeUpdate(preparedSql2, params)==1){
				row=super.executeUpdate(preparedSql1, params);
			}else{
				System.out.println("无法删除");
			}			
		} else {
			System.out.println("来了");
			row=super.executeUpdate(preparedSql1, params);
		}
		super.closeResource(rs);
		return row;
	}

	public static void main(String[] args) {
		DoctorDaoImpl sh = new DoctorDaoImpl();
		try {
			System.out.println(sh.deleteDoctor(13));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
